package com.chinar.funds.until;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.util.Log;

import com.chinar.funds.User;

import java.util.ArrayList;
import java.util.List;

public class FundDatabaseHelper extends SQLiteOpenHelper {
    private String TAG = "FundDatabaseHelper";
    private static final String DATABASE_NAME = "fund.db";
    private static final int DATABASE_VERSION = 4; // 更新数据库版本
    private static final String TABLE_FUND = "funds";
    private static final String COLUMN_CODE = "code";


    // 设置表相关常量
    private static final String TABLE_SETTINGS = "settings";
    private static final String COLUMN_SETTING_KEY = "setting_key";
    private static final String COLUMN_SETTING_VALUE = "setting_value";

    // 用户表相关常量
    private static final String TABLE_USERS = "users";
    private static final String COLUMN_USER_NAME = "user_name";
    private static final String COLUMN_USER_PWD = "user_pwd";
    private static final String COLUMN_USER_NICKNAME = "user_nickname";
    private static final String COLUMN_USER_SALT = "user_salt"; // 新增盐值字段

    public FundDatabaseHelper(Context context) {
        super(context, DATABASE_NAME, null, DATABASE_VERSION);
    }

    @Override
    public void onCreate(SQLiteDatabase db) {
        // 创建基金表
        String CREATE_TABLE = "CREATE TABLE " + TABLE_FUND + "("
                + COLUMN_CODE + " TEXT PRIMARY KEY)";
        db.execSQL(CREATE_TABLE);

        // 创建设置表
        String CREATE_SETTINGS_TABLE = "CREATE TABLE " + TABLE_SETTINGS + "("
                + COLUMN_SETTING_KEY + " TEXT PRIMARY KEY,"
                + COLUMN_SETTING_VALUE + " TEXT)";
        db.execSQL(CREATE_SETTINGS_TABLE);
        // 创建用户表（包含盐值字段）
        String CREATE_USERS_TABLE = "CREATE TABLE " + TABLE_USERS + "("
                + COLUMN_USER_NAME + " TEXT PRIMARY KEY,"
                + COLUMN_USER_PWD + " TEXT,"
                + COLUMN_USER_SALT + " TEXT,"
                + COLUMN_USER_NICKNAME + " TEXT)";
        db.execSQL(CREATE_USERS_TABLE);

        //插入默认的用户（使用加密密码）
        byte[] salt = PasswordUtils.generateSalt();
        String hashedPassword = PasswordUtils.hashPassword("123456", salt);
        
        ContentValues testuser = new ContentValues();
        testuser.put(COLUMN_USER_NAME, "13811302514");
        testuser.put(COLUMN_USER_PWD, hashedPassword);
        testuser.put(COLUMN_USER_SALT, PasswordUtils.bytesToHex(salt));
        testuser.put(COLUMN_USER_NICKNAME, "ROOT");
        db.insert(TABLE_USERS, null, testuser);
        //插入默认的基金数据
        ContentValues tesfund = new ContentValues();
        tesfund.put(COLUMN_CODE, "008955");

        db.insert(TABLE_FUND, null, tesfund);
        ContentValues tesfund2 = new ContentValues();
        tesfund2.put(COLUMN_CODE, "008507");
        db.insert(TABLE_FUND, null, tesfund2);

        // 插入默认的企业微信key
        ContentValues values = new ContentValues();
        values.put(COLUMN_SETTING_KEY, "wechat_key");
        values.put(COLUMN_SETTING_VALUE, "c5abeaf8-f071-4759-b17e-9b2cfa9ee37c");
        db.insert(TABLE_SETTINGS, null, values);

        // 插入默认的消息发送开关状态
        ContentValues switchValue = new ContentValues();
        switchValue.put(COLUMN_SETTING_KEY, "message_switch");
        switchValue.put(COLUMN_SETTING_VALUE, "false");
        db.insert(TABLE_SETTINGS, null, switchValue);
    }

    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
        if (oldVersion < 2) {
            // 在版本2中添加设置表
            String CREATE_SETTINGS_TABLE = "CREATE TABLE " + TABLE_SETTINGS + "("
                    + COLUMN_SETTING_KEY + " TEXT PRIMARY KEY,"
                    + COLUMN_SETTING_VALUE + " TEXT)";
            db.execSQL(CREATE_SETTINGS_TABLE);
            
            // 插入默认的企业微信key
            ContentValues values = new ContentValues();
            values.put(COLUMN_SETTING_KEY, "wechat_key");
            values.put(COLUMN_SETTING_VALUE, "c5abeaf8-f071-4759-b17e-9b2cfa9ee37c");
            db.insert(TABLE_SETTINGS, null, values);

            // 插入默认的消息发送开关状态
            ContentValues switchValue = new ContentValues();
            switchValue.put(COLUMN_SETTING_KEY, "message_switch");
            switchValue.put(COLUMN_SETTING_VALUE, "false");
            db.insert(TABLE_SETTINGS, null, switchValue);
        }
        
        if (oldVersion < 3) {
            // 在版本3中添加用户昵称字段
            // 创建新表
            db.execSQL("CREATE TABLE users_new (" 
                    + COLUMN_USER_NAME + " TEXT PRIMARY KEY,"
                    + COLUMN_USER_PWD + " TEXT,"
                    + COLUMN_USER_NICKNAME + " TEXT)");
            
            // 迁移旧数据到新表
            db.execSQL("INSERT INTO users_new SELECT *, '' FROM " + TABLE_USERS);
            
            // 删除旧表并重命名新表
            db.execSQL("DROP TABLE " + TABLE_USERS);
            db.execSQL("ALTER TABLE users_new RENAME TO " + TABLE_USERS);
        }
        
        // 版本4：添加盐值字段以支持密码加密
        if (oldVersion < 4) {
            // 添加盐值字段
            db.execSQL("ALTER TABLE " + TABLE_USERS + " ADD COLUMN " + COLUMN_USER_SALT + " TEXT");
            
            // 更新现有用户的密码，将其转换为加密形式
            updateExistingUsersPassword(db);
        }
    }
    
    /**
     * 更新现有用户的密码，将其转换为加密形式
     * @param db 数据库实例
     */
    private void updateExistingUsersPassword(SQLiteDatabase db) {
        Cursor cursor = db.query(TABLE_USERS, 
                new String[]{COLUMN_USER_NAME, COLUMN_USER_PWD}, 
                null, null, null, null, null);
        
        if (cursor.moveToFirst()) {
            do {
                String username = cursor.getString(0);
                String plainPassword = cursor.getString(1);
                
                // 对现有密码进行加密
                byte[] salt = PasswordUtils.generateSalt();
                String hashedPassword = PasswordUtils.hashPassword(plainPassword, salt);
                
                // 更新用户记录
                ContentValues values = new ContentValues();
                values.put(COLUMN_USER_PWD, hashedPassword);
                values.put(COLUMN_USER_SALT, PasswordUtils.bytesToHex(salt));
                
                db.update(TABLE_USERS, values, COLUMN_USER_NAME + "=?", new String[]{username});
            } while (cursor.moveToNext());
        }
        cursor.close();
    }

    // 插入基金代码
    public boolean insertFund(String code) {
        SQLiteDatabase db = this.getWritableDatabase();
        ContentValues values = new ContentValues();
        values.put(COLUMN_CODE, code);

        long result = db.insert(TABLE_FUND, null, values);
        db.close();
        return result != -1;
    }
    
    //插入用户
    public boolean insertUser(String name, String pwd, String nickname) {
        SQLiteDatabase db = this.getWritableDatabase();
        ContentValues values = new ContentValues();
        
        // 对密码进行加密
        byte[] salt = PasswordUtils.generateSalt();
        String hashedPassword = PasswordUtils.hashPassword(pwd, salt);
        
        values.put(COLUMN_USER_NAME, name);
        values.put(COLUMN_USER_PWD, hashedPassword);
        values.put(COLUMN_USER_SALT, PasswordUtils.bytesToHex(salt));
        values.put(COLUMN_USER_NICKNAME, nickname);
        Log.d(TAG, "insertUser: " + values);
        long result = db.insert(TABLE_USERS, null, values);
        Log.d(TAG, "insertUser: " + result);
        db.close();
        return result != -1;
    }
    
    // 查询是否存在
    public boolean isFundExists(String code) {
        SQLiteDatabase db = this.getReadableDatabase();
        Cursor cursor = db.query(TABLE_FUND, new String[]{COLUMN_CODE},
                COLUMN_CODE + "=?", new String[]{code}, null, null, null);
        boolean exists = cursor.moveToFirst();
        cursor.close();
        db.close();
        return exists;
    }
    
    //获取用户信息
    public List<User> getUserInfo(String name) {
        List<User> users = new ArrayList<>();
        SQLiteDatabase db = this.getReadableDatabase();
        Cursor cursor = db.query(TABLE_USERS, new String[]{COLUMN_USER_NAME, COLUMN_USER_PWD, COLUMN_USER_NICKNAME},
                COLUMN_USER_NAME + "=?", new String[]{name}, null, null, null);
        while (cursor.moveToNext()) {
            User user = new User(cursor.getString(0), cursor.getString(1), cursor.getString(2));
            users.add(user);
        }
        cursor.close();
        db.close();
        return users;
    }
    
    public List<User> userLogin(String name, String pwd) {
        List<User> users = new ArrayList<>();
        SQLiteDatabase db = this.getReadableDatabase();
        Cursor cursor = null;

        try {
            // 执行查询，只根据用户名查找用户
            cursor = db.query(TABLE_USERS, 
                    new String[]{COLUMN_USER_NAME, COLUMN_USER_PWD, COLUMN_USER_NICKNAME, COLUMN_USER_SALT},
                    COLUMN_USER_NAME + "=?", new String[]{name}, null, null, null);

            // 遍历查询结果
            while (cursor != null && cursor.moveToNext()) {
                String username = cursor.getString(0);
                String hashedPassword = cursor.getString(1);
                String nickname = cursor.getString(2);
                String saltHex = cursor.getString(3);
                
                // 验证密码
                byte[] salt = PasswordUtils.hexToBytes(saltHex);
                if (PasswordUtils.verifyPassword(pwd, salt, hashedPassword)) {
                    User user = new User(username, pwd, nickname); // 注意：这里返回的是原始密码，仅用于界面显示
                    users.add(user);
                }
            }
        } catch (Exception e) {
            e.printStackTrace(); // 处理异常
        } finally {
            // 确保 Cursor 和 Database 关闭
            if (cursor != null) {
                cursor.close();
            }
            db.close();
        }

        return users;
    }
    
    // 获取所有基金代码
    public List<String> getAllFundCodes() {
        List<String> codes = new ArrayList<>();
        SQLiteDatabase db = this.getReadableDatabase();
        Cursor cursor = db.query(TABLE_FUND, new String[]{COLUMN_CODE}, null, null, null, null, null);

        if (cursor.moveToFirst()) {
            do {
                codes.add(cursor.getString(0));
            } while (cursor.moveToNext());
        }
        cursor.close();
        db.close();
        return codes;
    }

    // 删除基金代码
    public void deleteFund(String code) {
        SQLiteDatabase db = this.getWritableDatabase();
        db.delete(TABLE_FUND, COLUMN_CODE + "=?", new String[]{code});
        db.close();
    }

    // 获取设置值
    public String getSetting(String key, String defaultValue) {
        SQLiteDatabase db = this.getReadableDatabase();
        Cursor cursor = db.query(TABLE_SETTINGS, new String[]{COLUMN_SETTING_VALUE},
                COLUMN_SETTING_KEY + "=?", new String[]{key}, null, null, null);

        String value = defaultValue;
        if (cursor.moveToFirst()) {
            value = cursor.getString(0);
        }
        cursor.close();
        db.close();
        return value;
    }

    // 设置值
    public void setSetting(String key, String value) {
        SQLiteDatabase db = this.getWritableDatabase();
        ContentValues values = new ContentValues();
        values.put(COLUMN_SETTING_KEY, key);
        values.put(COLUMN_SETTING_VALUE, value);

        db.replace(TABLE_SETTINGS, null, values);
        db.close();
    }
}